﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace WindowsFormProject.Depo
{
   public class DataBaseProvider
    {
        private ArrayList _Parameters = new ArrayList();

        private SqlCommand CreateCommand(string commandText, CommandType commandType)
        {
            SqlCommand command = ConnectionProvider.CreateConnection().CreateCommand();

            command.CommandType = commandType;
            command.CommandText = commandText;
            return command;
        }

        private void ProcessParameters(SqlCommand command)
        {
            foreach (SqlParameter myParameter in this._Parameters)
            {
                command.Parameters.Add(myParameter);
            }
        }

        public void AddInParameter(string parameterName, DbType dbType, object value)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.DbType = dbType;
            myParameter.Value = value;
            _Parameters.Add(myParameter);

        }

        public void AddInParameter(string parameterName, SqlDbType dbType, object value, int size)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.SqlDbType = dbType;
            myParameter.Value = value;
            myParameter.Size = size;
            _Parameters.Add(myParameter);

        }

        public void AddInParameter(string parameterName, SqlDbType dbType, object value)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.SqlDbType = dbType;
            myParameter.Value = value;
            _Parameters.Add(myParameter);

        }

        public void AddOutParameter(string parameterName, DbType dbType)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.DbType = dbType;
            myParameter.Direction = ParameterDirection.Output;
            _Parameters.Add(myParameter);

        }


        public void AddOutParameter(string parameterName, SqlDbType DbType)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.SqlDbType = DbType;
            myParameter.Direction = ParameterDirection.Output;
            _Parameters.Add(myParameter);

        }
        public void AddOutParameter(string parameterName, SqlDbType DbType, ParameterDirection direction)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.SqlDbType = DbType;
            myParameter.Direction = direction;
            _Parameters.Add(myParameter);

        }
        public void AddOutParameter(string parameterName, SqlDbType DbType, int size)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.SqlDbType = DbType;
            myParameter.Size = size;
            myParameter.Direction = ParameterDirection.Output;
            _Parameters.Add(myParameter);

        }
        public void AddOutParameter(string parameterName, SqlDbType DbType, object value)
        {
            SqlParameter myParameter = new SqlParameter();
            myParameter.ParameterName = parameterName;
            myParameter.SqlDbType = DbType;
            myParameter.Value = value;
            myParameter.Direction = ParameterDirection.Output;
            _Parameters.Add(myParameter);

        }

        public object GetParameterValue(string parameterName)
        {
            object returnValue = null;
            foreach (SqlParameter myParameter in _Parameters)
            {
                if (myParameter.ParameterName == parameterName)
                {
                    returnValue = myParameter.Value;
                }
            }
            return returnValue;
        }

        public void ClearParameters()
        {
            _Parameters.Clear();
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType)
        {
            int returnValue = 0;

            SqlCommand myCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myCommand);

            try
            {
                myCommand.Connection.Open();

                returnValue = myCommand.ExecuteNonQuery();

                myCommand.Connection.Close();

            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;

        }

        public decimal ExecuteNonQuery(string commandText, CommandType commandType, string OutputParameter)
        {
            decimal returnValue = 0;

            SqlCommand myCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myCommand);
            //try
            //{


            myCommand.Connection.Open();

            myCommand.ExecuteNonQuery();

            returnValue = Convert.ToDecimal(myCommand.Parameters[OutputParameter].Value);

            myCommand.Connection.Close();
            //}
            //catch (Exception ex)
            //{
            //    throw new DataBaseException(ex.Message);
            //}
            return returnValue;
        }

        public object ExecuteNonQueryReturnObject(string commandText, CommandType commandType, string OutputParameter)
        {
            object returnValue = 0;
            ////SqlTransaction tr;//
            SqlCommand myCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myCommand);
            try
            {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
                returnValue = myCommand.Parameters[OutputParameter].Value;
                myCommand.Connection.Close();
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }


        public int ExecuteNonQuery1(string commandText, CommandType commandType)
        {
            int returnValue = 0;
            SqlCommand myCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myCommand);
            try
            {
                myCommand.Connection.Open();
                returnValue = myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }


        public object ExecuteScalar(string commandText, CommandType commandType)
        {
            object returnValue = null;
            SqlCommand myCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myCommand);
            try
            {
                myCommand.Connection.Open();
                returnValue = myCommand.ExecuteScalar();
                myCommand.Connection.Close();

            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }

        public IDataReader ExecuteReader(string commandText, CommandType commandType)
        {
            IDataReader returnValue;
            SqlCommand myCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myCommand);
            try
            {
                myCommand.Connection.Open();
                returnValue = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }

        public DataSet ExecuteDataSet(string commandText, CommandType commandType)
        {
            DataSet returnValue = new DataSet();
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myAdapter.SelectCommand);
            try
            {
                myAdapter.Fill(returnValue);
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }

        public DataSet ExecuteDataSet(string commandText, CommandType commandType, string dataTableName)
        {
            DataSet returnValue = new DataSet();
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myAdapter.SelectCommand);
            try
            {
                myAdapter.Fill(returnValue, dataTableName);
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }

        public DataTable ExecuteDataTable(string commandText, CommandType commandType)
        {
            DataTable returnValue = new DataTable();
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myAdapter.SelectCommand);
            try
            {
                myAdapter.Fill(returnValue);
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }
            return returnValue;
        }

        public DataTable ExecuteDataTable(string commandText, CommandType commandType, string dataTableName)
        {
            DataTable returnValue = new DataTable(dataTableName);
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            myAdapter.SelectCommand = this.CreateCommand(commandText, commandType);
            this.ProcessParameters(myAdapter.SelectCommand);
            //try
            //{
            myAdapter.Fill(returnValue);
            //}
            //catch (Exception ex)
            //{
            //    throw new DataBaseException(ex.Message);
            //}
            return returnValue;
        }

        public void DataTableGuncelle(string commandText, CommandType commandType, DataTable Tablo1)
        {
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter();
            MyDataAdapter.SelectCommand = this.CreateCommand(commandText, commandType);
            SqlCommandBuilder CommandBuilder = new SqlCommandBuilder();
            CommandBuilder.DataAdapter = MyDataAdapter;
            try
            {
                //MyDataAdapter.Update(Tablo1.GetChanges(DataRowState.Added));
                if (Tablo1.GetChanges(DataRowState.Added) != null)
                {
                    MyDataAdapter.Update(Tablo1.GetChanges(DataRowState.Added));
                }
                if (Tablo1.GetChanges(DataRowState.Modified) != null)
                {
                    MyDataAdapter.Update(Tablo1.GetChanges(DataRowState.Modified));
                }
            }
            catch (Exception ex)
            {
                throw new DataBaseException(ex.Message);
            }

        }


    }
  
}



